﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using IHISCETeim.Library;


namespace IHISCETeim.Web.Export.FTradeModel
{
    public partial class FTradeExportUpKeep : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "应收帐款_财务部维保费导出.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "sheet1";
            }

        }




        #region 属性 (查询条件)


        protected string JingXiaoShang
        {
            get
            {
                return GetRequestValue<string>("JingXiaoShang", "");

            }
        }
        protected string HeTongHao
        {
            get
            {
                return GetRequestValue<string>("HeTongHao", "");

            }
        }

        protected string JiHao
        {
            get
            {
                return GetRequestValue<string>("JiHao", "");

            }
        }

        protected string JiQiState
        {
            get
            {
                return GetRequestValue<string>("JiQiState", "-");
            }
        }
        protected string XiaoShouLeiXing
        {
            get
            {
                return GetRequestValue<string>("XiaoShouLeiXing", "");
            }

        }
        protected string YingYeJingLi
        {
            get
            {
                return GetRequestValue<string>("YingYeJingLi", "");
            }

        }
        protected string XiaoShouShiJianFrom
        {
            get
            {
                return GetRequestValue<string>("XiaoShouShiJianFrom", "");

            }
        }
        protected string XiaoShouShiJianTo
        {
            get
            {
                if (string.IsNullOrEmpty(XiaoShouShiJianFrom))
                {
                    return "";
                }
                else
                {
                    string todate = GetRequestValue<string>("XiaoShouShiJianTo", "");
                    if (string.IsNullOrEmpty(todate))
                        return Convert.ToDateTime(XiaoShouShiJianFrom).AddDays(1).ToShortDateString();
                    else
                        return todate;
                }

            }
        }

        protected string InputDateFrom
        {
            get
            {
                return GetRequestValue<string>("InputDateFrom", "");
            }
        }
        protected string InputDateTo
        {
            get
            {
                if (string.IsNullOrEmpty(InputDateFrom))
                {
                    return "";
                }
                else
                {
                    string todate = GetRequestValue<string>("InputDateTo", "");
                    if (string.IsNullOrEmpty(todate))
                        return Convert.ToDateTime(InputDateFrom).AddDays(1).ToShortDateString();
                    else
                        return todate;
                }

            }
        }


        #endregion
        
        protected string GetSQLWhere()
        {
            string wheresql = "";
            string sqlcommonFiled = "";
            if (!string.IsNullOrEmpty(JingXiaoShang))
            {
                sqlcommonFiled = QueryOperatorsEnumHelper.ConvertCommonOperator("JingXiaoShang", QueryOperatorsEnum.等于, JingXiaoShang);
                wheresql = sqlcommonFiled;
            }



            if (!string.IsNullOrEmpty(HeTongHao))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("HeTongHao", QueryOperatorsEnum.等于, HeTongHao);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }
            if (!string.IsNullOrEmpty(YingYeJingLi))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("YingYeJingLi", QueryOperatorsEnum.等于, YingYeJingLi);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }

            if (!string.IsNullOrEmpty(XiaoShouShiJianFrom))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("XiaoShouShiJian", QueryOperatorsEnum.范围, XiaoShouShiJianFrom + "|" + XiaoShouShiJianTo);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }

            //string sqlAgentName = "";
            if (!string.IsNullOrEmpty(InputDateFrom))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator(" InputDate ", QueryOperatorsEnum.范围, InputDateFrom + "|" + InputDateTo);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }

            string dutySQL = "";
            if (!string.IsNullOrEmpty(JiHao))
            {
                dutySQL = QueryOperatorsEnumHelper.ConvertCommonOperator("JiHao", QueryOperatorsEnum.包含, JiHao);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + dutySQL;
                }
                else
                {
                    wheresql = dutySQL;
                }
            }

            if (JiQiState != "-")
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("JiQiState", QueryOperatorsEnum.等于, JiQiState);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }
            if (XiaoShouLeiXing != "")
            {
                string[] xslxs = XiaoShouLeiXing.Split(new char[] { ',' });
                string xslxsql = " XiaoShouLeiXing in (''";
                foreach (string itm in xslxs)
                {
                    if (!string.IsNullOrEmpty(itm))
                    {
                        xslxsql += string.Format(",'{0}'", itm);
                    }
                }
                xslxsql += ")";

                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + xslxsql;
                }
                else
                {
                    wheresql = xslxsql;
                }
            }

            return wheresql;
        }



        private DataTableCollection _dts = null;
        protected DataTableCollection dts
        {
            get
            {
                if (_dts == null)
                {
                    
                    _dts = FTradeReportController.GetUpKeep(GetSQLWhere());
                }
                return _dts;
            }
        }
        private DataTable dtUpDetail = null;
        protected void Page_Load(object sender, EventArgs e)
        {
            InitializeWorkbook();
            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            //


            DataTable dtList = dts[0];
           // dtUpDetail = dts[1];

            int rowindexBase = 1;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;

            int FTradeID = 0;
            foreach (DataRow dr in dtList.Rows)
            {
                FTradeID = Convert.ToInt32(dr["FTradeID"]);//ID
                HSSFRow writerow = (HSSFRow)sheet1.CreateRow(rowReal);

                writerow.CreateCell(0).SetCellValue(dr["XiaoShouShiJian"].ToString());
                writerow.CreateCell(1).SetCellValue(dr["JiQiState"].ToString());

                writerow.CreateCell(2).SetCellValue(dr["JingXiaoShang"].ToString());
                writerow.CreateCell(3).SetCellValue(dr["HeTongHao"].ToString());
                writerow.CreateCell(4).SetCellValue(dr["XingHao"].ToString());
                writerow.CreateCell(5).SetCellValue(dr["JiHao"].ToString());
                writerow.CreateCell(6).SetCellValue(GetDBDouble(dr["ShuLiang"]));
                writerow.CreateCell(7).SetCellValue(GetDBDouble(dr["HeTongJinE"]));

                writerow.CreateCell(8).SetCellValue(dr["Title"].ToString());
                writerow.CreateCell(9).SetCellValue(GetDBDouble(dr["JinE"]));
                writerow.CreateCell(10).SetCellValue(GetDBDouble(dr["JinE2"]));
                writerow.CreateCell(11).SetCellValue(dr["Remark"].ToString());
                writerow.CreateCell(12).SetCellValue(dr["InputDate"].ToString());
                rowReal++;
            }

                  

            //
            WriteToFile();
        }
        /// <summary>
        /// 获取 机器维保费收入记录(FTradeUpKeep) 相关值
        /// </summary>
        /// <param name="FTradeID"></param>
        /// <param name="title"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        private object GetUpKeepVal(int FTradeID, string title,string columnName)
        {
            object xx = columnName.ToLower() == "jine" ? "0" : "";
            try
            {
                DataTable deftrade = dtUpDetail.Select(string.Format("FTradeID={0} and Title='{1}' ", FTradeID, title)).CopyToDataTable();

                if (deftrade.Rows.Count > 0)
                {
                    foreach(DataRow dr in deftrade.Rows)
                    {
                        if (columnName.ToLower() == "jine")
                        {
                            xx = Convert.ToDecimal(xx) + Convert.ToDecimal(dr[columnName]);
                        }
                        else {
                            xx = xx.ToString() + dr[columnName].ToString();
                        }
                        //return deftrade.Rows[0][columnName];
                    }
                }
                return xx;
            }
            catch
            {
                
            }
            return columnName.ToLower() == "jine" ? "0" : "";

        }

        private Double GetDBDouble(object v)
        {
            if (v is DBNull)
                return 0;
            return Convert.ToDouble(v);
        }
        private string GetDBString(object v)
        {
            if (v is DBNull)
                return "";
            return v.ToString();
        }

        protected override string GetXlsFileName(string oldFileName)
        {
            //return base.GetXlsFileName(oldFileName);

            return string.Format("{0}{1}.xls", "维修保养费", DateTime.Now.ToString("yyyy-MM-dd")).Trim();
        }
    }
}